IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMaxEventLogId' AND type = 'P')
     DROP PROCEDURE dbo.SaveMaxEventLogId
GO

CREATE PROCEDURE dbo.SaveMaxEventLogId
     @SrvrInstncDatabaseId  int,
     @MaxEventLogId         varchar(20) = NULL OUTPUT, -- Optional Parameter
     @SuppressResultsetFlag tinyint     = 0 -- Optional Parameter
AS
/*
 *
 * SaveMaxEventLogId
 *
 * Purpose:      To Calculate and save the MaxEventLogId
 *               for the specified server.
 *
 * Inputs:       @SrvrInstncDatabaseId
 *
 * Assumes:      
 *
 * Effects:      Database is updated, or record is added
 *
 * Returns:      Nothing
 *
 * Note(s):      As a paranoia step if you force @SrvrInstncDatabaseId to NULL, 
 *               it will update ALL Max EventLog Ids for any server instance
 *               that has history.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/04/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @ParameterId  INT

     -- We need this everywhere so we set it early.
     SELECT @ParameterId  = dbo.GetParameterIdByParameterCd( 'MaxEventLogId' )
     
     -- We want to make sure we start at NULL
     SELECT @MaxEventLogId = NULL
     
     -- Determine if we are doing one or all.
     IF ( @SrvrInstncDatabaseId IS NOT NULL )
     BEGIN -- Doing one.
          DECLARE @SrvrInstncId     int,
                  --@MaxEventLogId    bigint, -- declared above.
                  --@ParameterMultiId int,
                  @msg              varchar(80)

          -- Determine max id.
          SELECT @MaxEventLogId = MAX(EventLogId)
          FROM   dbo.MasterEventLog 
          WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId 

          -- Get the Server Instance Id
          SELECT @SrvrInstncId = SrvrInstncId
          FROM   dbo.SrvrInstncDatabase
          WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId

          -- Determine if record exists
          IF ( dbo.GetParameterCdExistsBySrvrInstncIdMultiId( @SrvrInstncId, 
                                                              @SrvrInstncDatabaseId,
                                                              'MaxEventLogId' ) = 1 )
          BEGIN -- Record already exists so we do update
               UPDATE dbo.SrvrInstncEAV
               SET    ParameterValue = @MaxEventLogId,
                      LastModDt      = CURRENT_TIMESTAMP 
               WHERE  SrvrInstncId     = @SrvrInstncId 
               AND    ParameterId      = @ParameterId
               AND    ParameterMultiId = @SrvrInstncDatabaseId -- @ParameterMultiId 

               IF ( @@ROWCOUNT != 1 )
               BEGIN
                    SELECT @msg = 'The update of the MaxEventLogId for SrvrInstncDatabaseId: '
                                + CONVERT( varchar(11), @SrvrInstncDatabaseId )
                                + ' Failed'

                    RAISERROR( @msg, 16, 1 )
               END
               ELSE
               BEGIN
                    -- Return resultset if allowed
                    IF ( @SuppressResultsetFlag != 1 )
                    BEGIN
                         SELECT SrvrInstncId,
                                ParameterId,
                                ParameterMultiId,
                                ParameterValue
                         FROM   dbo.SrvrInstncEAV
                         WHERE  SrvrInstncId      = @SrvrInstncId
                         AND    ParameterId       = @ParameterId
                         AND    ParameterMultiId  = @SrvrInstncDatabaseId
                    END
               END
          END
          ELSE
          BEGIN
               INSERT dbo.SrvrInstncEAV
                    ( SrvrInstncId,
                      ParameterId,
                      ParameterMultiId,
                      ParameterValue )
               SELECT @SrvrInstncId,
                      @ParameterId,
                      @SrvrInstncDatabaseId,
                      @MaxEventLogId 

               IF ( @@ROWCOUNT != 1 )
               BEGIN
                    SELECT @msg = 'The insert of the MaxEventLogId for SrvrInstncDatabaseId: '
                                + CONVERT( varchar(11), @SrvrInstncDatabaseId )
                                + ' Failed'

                    RAISERROR( @msg, 16, 1 )
               END
               ELSE
               BEGIN
                    -- Return resultset if allowed
                    IF ( @SuppressResultsetFlag != 1 )
                    BEGIN
                         SELECT SrvrInstncId,
                                ParameterId,
                                ParameterMultiId,
                                ParameterValue
                         FROM   dbo.SrvrInstncEAV
                         WHERE  SrvrInstncId      = @SrvrInstncId
                         AND    ParameterId       = @ParameterId
                         AND    ParameterMultiId  = @SrvrInstncDatabaseId
                    END
               END
          END
     END
     ELSE 
     BEGIN -- @ServerInstanceId is NULL, do all of them.
          DECLARE @Server TABLE ( SrvrInstncId         int           NOT NULL,
                                  SrvrInstncDatabaseId int           NOT NULL PRIMARY KEY,
                                  MaxEventLogId        varchar(8000) NOT NULL )

          -- Get all server ids and maximums
          INSERT @Server 
               ( SrvrInstncId,
                 SrvrInstncDatabaseId,
                 MaxEventLogId )
          SELECT sid.SrvrInstncId,
                 mel.SrvrInstncDatabaseId,
                 MAX(mel.EventLogId)
          FROM   dbo.MasterEventLog mel INNER JOIN dbo.SrvrInstncDatabase sid
                    ON mel.SrvrInstncDatabaseId = sid.SrvrInstncDatabaseId
          GROUP BY sid.SrvrInstncId, mel.SrvrInstncDatabaseId 

          -- Update any that already exist, we do this first on purpose.
          UPDATE dbo.SrvrInstncEAV
          SET    ParameterValue = s.MaxEventLogId,
                 LastModDt      = CURRENT_TIMESTAMP 
          FROM   dbo.SrvrInstncEAV sie INNER JOIN @Server s
                    ON sie.SrvrInstncId     = s.SrvrInstncId
                   AND sie.ParameterMultiId = s.SrvrInstncDatabaseId
                   AND sie.ParameterId      = @ParameterId
          WHERE  sie.ParameterValue != s.MaxEventLogId 
          OR     sie.ParameterValue IS NULL

          -- Insert the new ones, making extra sure that we don't add a dupe
          INSERT dbo.SrvrInstncEAV
               ( SrvrInstncId,
                 ParameterId,
                 ParameterMultiId,
                 ParameterValue )
          SELECT s.SrvrInstncId,
                 @ParameterId,
                 s.SrvrInstncDatabaseId,
                 s.MaxEventLogId 
          FROM   @Server s
          WHERE  NOT EXISTS ( SELECT 'X' 
                              FROM   dbo.SrvrInstncEAV siem
                              WHERE  siem.SrvrInstncId     = s.SrvrInstncId
                              AND    siem.ParameterId      = @ParameterId
                              AND    siem.ParameterMultiId = s.SrvrInstncDatabaseId  )

          -- Return resultset if allowed
          IF ( @SuppressResultsetFlag != 1 )
          BEGIN
               SELECT siem.SrvrInstncId,
                      siem.ParameterId,
                      siem.ParameterMultiId,
                      siem.ParameterValue
               FROM   dbo.SrvrInstncEAV siem INNER JOIN @Server s
                         ON siem.SrvrInstncId     = s.SrvrInstncId
                        AND siem.ParameterMultiId = s.SrvrInstncDatabaseId
                        AND siem.ParameterId      = @ParameterId
          END

     END
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Calculate and save the maximum EventLogId in MasterEventLog for a specified server instance and database on that server instance.  This is used to determine what records are retrieved during the next collection run so that the whole table is not collected and only the new records (as far as the MasterEventLog is concerned) are retrieved.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveMaxEventLogId;
GO
